<!DOCTYPE html><html lang="en" data-theme="light"><head><meta charset="UTF-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width, initial-scale=1.0,viewport-fit=cover"><title>学习笔记10.27 | 小潘的个人博客</title><meta name="author" content="John Doe"><meta name="copyright" content="John Doe"><meta name="format-detection" content="telephone=no"><meta name="theme-color" content="#ffffff"><meta name="description" content="1.autowired注解@Autowired    private EmpMapper empMapper; 这是一个Java代码片段，用于自动注入EmpMapper对象。这里的@Autowired注解表示使用Spring框架的依赖注入功能，而empMapper则是一个EmpMapper接口的实现。当这个类被创建时，Spring会自动创建一个EmpMapper对象并将其注入到这个类中。 问：那还">
<meta property="og:type" content="article">
<meta property="og:title" content="学习笔记10.27">
<meta property="og:url" content="https://t0wolf.gitee.io/xiaopanblog/2023/10/26/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B010.27/index.html">
<meta property="og:site_name" content="小潘的个人博客">
<meta property="og:description" content="1.autowired注解@Autowired    private EmpMapper empMapper; 这是一个Java代码片段，用于自动注入EmpMapper对象。这里的@Autowired注解表示使用Spring框架的依赖注入功能，而empMapper则是一个EmpMapper接口的实现。当这个类被创建时，Spring会自动创建一个EmpMapper对象并将其注入到这个类中。 问：那还">
<meta property="og:locale" content="en_US">
<meta property="og:image" content="https://i.loli.net/2021/02/24/5O1day2nriDzjSu.png">
<meta property="article:published_time" content="2023-10-26T15:10:39.000Z">
<meta property="article:modified_time" content="2023-10-27T14:47:59.590Z">
<meta property="article:author" content="John Doe">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://i.loli.net/2021/02/24/5O1day2nriDzjSu.png"><link rel="shortcut icon" href="./img/favicon.png"><link rel="canonical" href="https://t0wolf.gitee.io/xiaopanblog/2023/10/26/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B010.27/index.html"><link rel="preconnect" href="//cdn.jsdelivr.net"/><link rel="preconnect" href="//busuanzi.ibruce.info"/><link rel="stylesheet" href="./css/index.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free/css/all.min.css" media="print" onload="this.media='all'"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fancyapps/ui/dist/fancybox/fancybox.min.css" media="print" onload="this.media='all'"><script>const GLOBAL_CONFIG = {
  root: './',
  algolia: undefined,
  localSearch: undefined,
  translate: undefined,
  noticeOutdate: undefined,
  highlight: {"plugin":"highlighjs","highlightCopy":true,"highlightLang":true,"highlightHeightLimit":false},
  copy: {
    success: 'Copy Successful',
    error: 'Copy Error',
    noSupport: 'Browser Not Supported'
  },
  relativeDate: {
    homepage: false,
    post: false
  },
  runtime: '',
  dateSuffix: {
    just: 'Just now',
    min: 'minutes ago',
    hour: 'hours ago',
    day: 'days ago',
    month: 'months ago'
  },
  copyright: undefined,
  lightbox: 'fancybox',
  Snackbar: undefined,
  infinitegrid: {
    js: 'https://cdn.jsdelivr.net/npm/@egjs/infinitegrid/dist/infinitegrid.min.js',
    buttonText: 'Load More'
  },
  isPhotoFigcaption: false,
  islazyload: false,
  isAnchor: false,
  percent: {
    toc: true,
    rightside: false,
  },
  autoDarkmode: false
}</script><script id="config-diff">var GLOBAL_CONFIG_SITE = {
  title: '学习笔记10.27',
  isPost: true,
  isHome: false,
  isHighlightShrink: false,
  isToc: true,
  postUpdate: '2023-10-27 22:47:59'
}</script><script>(win=>{
      win.saveToLocal = {
        set: (key, value, ttl) => {
          if (ttl === 0) return
          const now = Date.now()
          const expiry = now + ttl * 86400000
          const item = {
            value,
            expiry
          }
          localStorage.setItem(key, JSON.stringify(item))
        },
      
        get: key => {
          const itemStr = localStorage.getItem(key)
      
          if (!itemStr) {
            return undefined
          }
          const item = JSON.parse(itemStr)
          const now = Date.now()
      
          if (now > item.expiry) {
            localStorage.removeItem(key)
            return undefined
          }
          return item.value
        }
      }
    
      win.getScript = (url, attr = {}) => new Promise((resolve, reject) => {
        const script = document.createElement('script')
        script.src = url
        script.async = true
        script.onerror = reject
        script.onload = script.onreadystatechange = function() {
          const loadState = this.readyState
          if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
          script.onload = script.onreadystatechange = null
          resolve()
        }

        Object.keys(attr).forEach(key => {
          script.setAttribute(key, attr[key])
        })

        document.head.appendChild(script)
      })
    
      win.getCSS = (url, id = false) => new Promise((resolve, reject) => {
        const link = document.createElement('link')
        link.rel = 'stylesheet'
        link.href = url
        if (id) link.id = id
        link.onerror = reject
        link.onload = link.onreadystatechange = function() {
          const loadState = this.readyState
          if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
          link.onload = link.onreadystatechange = null
          resolve()
        }
        document.head.appendChild(link)
      })
    
      win.activateDarkMode = () => {
        document.documentElement.setAttribute('data-theme', 'dark')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#0d0d0d')
        }
      }
      win.activateLightMode = () => {
        document.documentElement.setAttribute('data-theme', 'light')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#ffffff')
        }
      }
      const t = saveToLocal.get('theme')
    
        if (t === 'dark') activateDarkMode()
        else if (t === 'light') activateLightMode()
      
      const asideStatus = saveToLocal.get('aside-status')
      if (asideStatus !== undefined) {
        if (asideStatus === 'hide') {
          document.documentElement.classList.add('hide-aside')
        } else {
          document.documentElement.classList.remove('hide-aside')
        }
      }
    
      const detectApple = () => {
        if(/iPad|iPhone|iPod|Macintosh/.test(navigator.userAgent)){
          document.documentElement.classList.add('apple')
        }
      }
      detectApple()
    })(window)</script><meta name="generator" content="Hexo 6.3.0"><link rel="alternate" href="./atom.xml" title="小潘的个人博客" type="application/atom+xml">
</head><body><div id="sidebar"><div id="menu-mask"></div><div id="sidebar-menus"><div class="avatar-img is-center"><img src="https://i.loli.net/2021/02/24/5O1day2nriDzjSu.png" onerror="onerror=null;src='/img/friend_404.gif'" alt="avatar"/></div><div class="sidebar-site-data site-data is-center"><a href="./archives/"><div class="headline">Articles</div><div class="length-num">10</div></a><a href="./tags/"><div class="headline">Tags</div><div class="length-num">1</div></a><a href="./categories/"><div class="headline">Categories</div><div class="length-num">0</div></a></div><hr class="custom-hr"/></div></div><div class="post" id="body-wrap"><header class="post-bg" id="page-header"><nav id="nav"><span id="blog-info"><a href="./" title="小潘的个人博客"><span class="site-name">小潘的个人博客</span></a></span><div id="menus"><div id="toggle-menu"><a class="site-page" href="javascript:void(0);"><i class="fas fa-bars fa-fw"></i></a></div></div></nav><div id="post-info"><h1 class="post-title">学习笔记10.27</h1><div id="post-meta"><div class="meta-firstline"><span class="post-meta-date"><i class="far fa-calendar-alt fa-fw post-meta-icon"></i><span class="post-meta-label">Created</span><time class="post-meta-date-created" datetime="2023-10-26T15:10:39.000Z" title="Created 2023-10-26 23:10:39">2023-10-26</time><span class="post-meta-separator">|</span><i class="fas fa-history fa-fw post-meta-icon"></i><span class="post-meta-label">Updated</span><time class="post-meta-date-updated" datetime="2023-10-27T14:47:59.590Z" title="Updated 2023-10-27 22:47:59">2023-10-27</time></span></div><div class="meta-secondline"><span class="post-meta-separator">|</span><span class="post-meta-pv-cv" id="" data-flag-title="学习笔记10.27"><i class="far fa-eye fa-fw post-meta-icon"></i><span class="post-meta-label">Post Views:</span><span id="busuanzi_value_page_pv"><i class="fa-solid fa-spinner fa-spin"></i></span></span></div></div></div></header><main class="layout" id="content-inner"><div id="post"><article class="post-content" id="article-container"><h2 id="1-autowired注解"><a href="#1-autowired注解" class="headerlink" title="1.autowired注解"></a>1.autowired注解</h2><p><code>@Autowired</code><br>    <code>private EmpMapper empMapper;</code></p>
<p>这是一个Java代码片段，用于自动注入EmpMapper对象。这里的@Autowired注解表示使用Spring框架的依赖注入功能，而empMapper则是一个EmpMapper接口的实现。当这个类被创建时，Spring会自动创建一个EmpMapper对象并将其注入到这个类中。</p>
<p>问：那还要重写接口中的方法吗</p>
<p>答：通常情况下，如果使用@Autowired注解来注入一个接口的实现，那么不需要重写接口中的方法。因为@Autowired注解会自动调用实现类中的方法。</p>
<p>例如，如果有一个接口EmpMapper，它有一个方法selectEmpById，那么在EmpMapper的实现类中，可以不重写这个方法，Spring会自动调用实现类中的selectEmpById方法。</p>
<p>但是，如果EmpMapper接口中定义的方法需要传入参数，那么在实现类中也需要传入相应的参数。例如，如果EmpMapper接口中定义了一个方法selectEmpByName，它需要传入一个String类型的参数name，那么在实现类中也需要传入一个String类型的参数name。</p>
<p>总之，使用@Autowired注解可以简化依赖注入的实现，而不需要显式地重写接口中的方法。</p>
<h2 id="2-javaweb资料mybatis配置"><a href="#2-javaweb资料mybatis配置" class="headerlink" title="2.javaweb资料mybatis配置"></a>2.javaweb资料mybatis配置</h2><p>1.application.properties配置文件中</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">#配置mybatis的日志, 指定输出到控制台</span><br><span class="line">mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl</span><br></pre></td></tr></table></figure>

<p><img src="./xiaopanblog/2023/10/26/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B010.27/image-20231027092857687.png" alt="image-20231027092857687"></p>
<p>2.sql注入问题</p>
<p><img src="./xiaopanblog/2023/10/26/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B010.27/image-20231027093100524.png" alt="image-20231027093100524"></p>
<p>3.主键返回</p>
<p><img src="./xiaopanblog/2023/10/26/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B010.27/image-20231027093630632.png" alt="image-20231027093630632"></p>
<p>4.xml映射文件</p>
<p><img src="./xiaopanblog/2023/10/26/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B010.27/image-20231027125023204.png" alt="image-20231027125023204"></p>
<p>5.MybatisX 是一款基于 IDEA 的快速开发Mybatis的插件，为效率而生。</p>
<p>6.动态sql</p>
<p><img src="./xiaopanblog/2023/10/26/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B010.27/image-20231027130259704.png" alt="image-20231027130259704"></p>
<p><img src="./xiaopanblog/2023/10/26/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B010.27/image-20231027131128882.png" alt="image-20231027131128882"></p>
<h3 id><a href="#" class="headerlink" title></a><set></set></h3><p>动态地在行首插入 SET 关键字，并会删掉额外的逗号。（用在update语句中）</p>
<h3 id="-1"><a href="#-1" class="headerlink" title></a><foreach></foreach></h3><p>sql 语句：<code>delete from emp where id in (1,2,3);</code></p>
<p>接口方法：<code>public void deleteByIds(List&lt;Integer&gt;  ids);</code></p>
<p>xml映射文件：</p>
<p>&#96;&#96;<delete id="deleteByIds"><code>    </code>delete from emp where id in<code>    </code><foreach collection="ids" item="id" separator="," open="(" close=")"><code>      </code>#{id}<code>    </code></foreach><code>  &lt;/delete&gt;</code></delete></p>
<p>声明：</p>
<p>lcollection：集合名称</p>
<p>litem：集合遍历出来的元素&#x2F;项</p>
<p>lseparator：每一次遍历使用的分隔符</p>
<p>lopen：遍历开始前拼接的片段</p>
<p>close：遍历结束后拼接的片段</p>
<h3 id="-2"><a href="#-2" class="headerlink" title></a><sql></sql></h3><p><img src="./xiaopanblog/2023/10/26/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B010.27/image-20231027132241009.png" alt="image-20231027132241009"></p>
<h2 id="3-MySQL复习"><a href="#3-MySQL复习" class="headerlink" title="3.MySQL复习"></a>3.MySQL复习</h2><h3 id="1-SQL"><a href="#1-SQL" class="headerlink" title="1.SQL"></a>1.SQL</h3><p>（<strong>S</strong>tructured <strong>Q</strong>uery <strong>L</strong>anguage，简称SQL）：结构化查询语言，它是操作关系型数据库的编程语言，定义了一套操作关系型数据库的统一标准。我们学习数据库开发，最为重要的就是学习SQL语句 。</p>
<p><img src="./xiaopanblog/2023/10/26/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B010.27/image-20231027134301039.png" alt="image-20231027134301039"></p>
<h3 id="2-关系型数据库（RDBMS）"><a href="#2-关系型数据库（RDBMS）" class="headerlink" title="2.关系型数据库（RDBMS）"></a>2.<strong>关系型数据库（RDBMS）</strong></h3><p>概念：建立在关系模型基础上，由多张相互连接的<strong>二维表</strong>组成的数据库。</p>
<p>通过MySQL客户端连接数据库管理系统DBMS，然后通过DBMS操作数据库</p>
<p>SQL语句根据其功能被分为四大类：DDL、DML、DQL、DCL </p>
<table>
<thead>
<tr>
<th><strong>分类</strong></th>
<th><strong>全称</strong></th>
<th><strong>说明</strong></th>
</tr>
</thead>
<tbody><tr>
<td>DDL</td>
<td>Data Definition  Language</td>
<td>数据定义语言，用来定义数据库对象(数据库，表，字段)</td>
</tr>
<tr>
<td>DML</td>
<td>Data Manipulation  Language</td>
<td>数据操作语言，用来对数据库表中的数据进行增删改</td>
</tr>
<tr>
<td>DQL</td>
<td>Data Query Language</td>
<td>数据查询语言，用来查询数据库中表的记录</td>
</tr>
<tr>
<td>DCL</td>
<td>Data Control  Language</td>
<td>数据控制语言，用来创建数据库用户、控制数据库的访问权限</td>
</tr>
</tbody></table>
<h3 id="3-DDL"><a href="#3-DDL" class="headerlink" title="3.DDL"></a>3.DDL</h3><p>show databases;</p>
<p>select database();查询当前数据库</p>
<p>create database [ if not exists ] 数据库名; 避免报错</p>
<p>drop database [ if exists ] 数据库名 ; 删除数据库</p>
<p><strong>说明：上述语法中的database，也可以替换成 schema</strong></p>
<ol>
<li>如：create schema db01;</li>
<li>如：show schemas;</li>
</ol>
<p>DataGrip这款工具可以不用安装，因为Jetbrains公司已经将DataGrip这款工具的功能已经集成到了 IDEA当中，所以我们就可以使用IDEA来作为一款图形化界面工具来操作Mysql数据库。</p>
<p><strong>表结构</strong></p>
<p>create table  表名(<br>    字段1  字段1类型 [约束]  [comment  字段1注释 ],<br>    字段2  字段2类型 [约束]  [comment  字段2注释 ],<br>    ……<br>    字段n  字段n类型 [约束]  [comment  字段n注释 ]<br>) [ comment  表注释 ] ;</p>
<p><img src="./xiaopanblog/2023/10/26/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B010.27/image-20231027141502229.png" alt="image-20231027141502229"></p>
<p>在MySQL数据库当中，提供了以下5种约束：</p>
<table>
<thead>
<tr>
<th><strong>约束</strong></th>
<th><strong>描述</strong></th>
<th><strong>关键字</strong></th>
</tr>
</thead>
<tbody><tr>
<td>非空约束</td>
<td>限制该字段值不能为null</td>
<td>not null</td>
</tr>
<tr>
<td>唯一约束</td>
<td>保证字段的所有数据都是唯一、不重复的</td>
<td>unique</td>
</tr>
<tr>
<td>主键约束</td>
<td>主键是一行数据的唯一标识，要求非空且唯一</td>
<td>primary key</td>
</tr>
<tr>
<td>默认约束</td>
<td>保存数据时，如果未指定该字段值，则采用默认值</td>
<td>default</td>
</tr>
<tr>
<td>外键约束</td>
<td>让两张表的数据建立连接，保证数据的一致性和完整性</td>
<td>foreign key</td>
</tr>
</tbody></table>
<p>主键自增：auto_increment</p>
<ul>
<li>每次插入新的行记录时，数据库自动生成id字段(主键)下的值</li>
<li>具有auto_increment的数据列是一个正数序列开始增长(从1开始自增)</li>
</ul>
<p><img src="./xiaopanblog/2023/10/26/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B010.27/image-20231027142146514.png" alt="image-20231027142146514"></p>
<p><strong>数据类型</strong></p>
<p>char 与 varchar 都可以描述字符串，char是定长字符串，指定长度多长，就占用多少个字符，和字段值的长度无关 。而varchar是变长字符串，指定的长度为最大占用长度 。相对来说，char的性能会更高些。</p>
<p>**</p>
<table>
<thead>
<tr>
<th>类型</th>
<th>大小</th>
<th>范围</th>
<th>格式</th>
<th>描述</th>
</tr>
</thead>
<tbody><tr>
<td>DATE</td>
<td>3</td>
<td>1000-01-01 至  9999-12-31</td>
<td>YYYY-MM-DD</td>
<td>日期值</td>
</tr>
<tr>
<td>TIME</td>
<td>3</td>
<td>-838:59:59 至  838:59:59</td>
<td>HH:MM:SS</td>
<td>时间值或持续时间</td>
</tr>
<tr>
<td>YEAR</td>
<td>1</td>
<td>1901 至 2155</td>
<td>YYYY</td>
<td>年份值</td>
</tr>
<tr>
<td>DATETIME</td>
<td>8</td>
<td>1000-01-01 00:00:00 至 9999-12-31 23:59:59</td>
<td>YYYY-MM-DD HH:MM:SS</td>
<td>混合日期和时间值</td>
</tr>
<tr>
<td>TIMESTAMP</td>
<td>4</td>
<td>1970-01-01 00:00:01 至 2038-01-19 03:14:07</td>
<td>YYYY-MM-DD HH:MM:SS</td>
<td>混合日期和时间值，时间戳</td>
</tr>
</tbody></table>
<p><strong>查询</strong></p>
<p>desc  表名 ; #可以查看指定表的字段、字段的类型、是否可以为NULL、是否存在默认值等信息</p>
<p><strong>修改</strong></p>
<p><strong>添加字段</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表名 <span class="keyword">add</span>  字段名  类型(长度)  [comment 注释]  [约束];</span><br></pre></td></tr></table></figure>

<p>案例： 为tb_emp表添加字段qq，字段类型为 varchar(11)</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> tb_emp <span class="keyword">add</span>  qq  <span class="type">varchar</span>(<span class="number">11</span>) comment <span class="string">&#x27;QQ号码&#x27;</span>;</span><br></pre></td></tr></table></figure>

<blockquote>
<p>图形化操作：添加字段</p>
</blockquote>
<p><strong>修改数据类型</strong></p>
<p>alter table 表名 modify  字段名  新数据类型(长度);</p>
<p>alter table 表名 change  旧字段名  新字段名  类型(长度)  [comment 注释]  [约束];</p>
<p><strong>删除字段</strong></p>
<p>alter table 表名 drop 字段名;</p>
<h3 id="4-DML"><a href="#4-DML" class="headerlink" title="4.DML"></a>4.DML</h3><p>insert into 表名 (字段名1, 字段名2) values (值1, 值2);</p>
<p>批量添加数据（指定字段）</p>
<p>insert into 表名 (字段名1, 字段名2) values (值1, 值2), (值1, 值2);</p>
<p>update 表名 set 字段名1 &#x3D; 值1 , 字段名2 &#x3D; 值2 , …. [where 条件] ;</p>
<p>update tb_emp set name&#x3D;’张三’,update_time&#x3D;now() where id&#x3D;1；</p>
<p>delete from 表名  [where  条件] ;</p>
<p>delete from tb_emp where id &#x3D; 1;</p>
<p>5.DQL</p>
<p>在一个正常的业务系统中，查询操作的使用频次是要远高于增删改操作的。当我们打开某个网站或APP所看到的展示信息，都是通过从数据库中查询得到的，而在这个查询过程中，还会涉及到条件、排序、分页等操作。</p>
<p>SELECT<br>    字段列表</p>
<p>FROM<br>    表名列表</p>
<p>WHERE<br>    条件列表</p>
<p>GROUP  BY<br>    分组字段列表</p>
<p>HAVING<br>    分组后条件列表</p>
<p>ORDER BY<br>    排序字段列表</p>
<p>LIMIT<br>    分页参数</p>
<ul>
<li>基本查询（不带任何条件）</li>
<li>条件查询（where）</li>
<li>分组查询（group by）</li>
<li>排序查询（order by）</li>
<li>分页查询（limit）</li>
</ul>
<p><strong>查询</strong></p>
<p>案例9：查询 姓名 为两个字的员工信息</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">select id, username, password, name, gender, image, job, entrydate, create_time, update_time</span><br><span class="line">from tb_emp</span><br><span class="line">where name like &#x27;__&#x27;;  # 通配符 &quot;_&quot; 代表任意1个字符</span><br></pre></td></tr></table></figure>



<p>案例10：查询 姓 ‘张’ 的员工信息</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">select id, username, password, name, gender, image, job, entrydate, create_time, update_time</span><br><span class="line">from tb_emp</span><br><span class="line">where name like &#x27;张%&#x27;; # 通配符 &quot;%&quot; 代表任意个字符（0个 ~ 多个）</span><br></pre></td></tr></table></figure>

<h3 id="聚合函数"><a href="#聚合函数" class="headerlink" title="聚合函数"></a>聚合函数</h3><p>之前我们做的查询都是横向查询，就是根据条件一行一行的进行判断，而使用聚合函数查询就是纵向查询，它是对一列的值进行计算，然后返回一个结果值。（将一列数据作为一个整体，进行纵向计算）</p>
<p> <code>select  聚合函数(字段列表)  from  表名 ;</code></p>
<p>常用聚合函数：</p>
<table>
<thead>
<tr>
<th><strong>函数</strong></th>
<th><strong>功能</strong></th>
</tr>
</thead>
<tbody><tr>
<td>count</td>
<td>统计数量</td>
</tr>
<tr>
<td>max</td>
<td>最大值</td>
</tr>
<tr>
<td>min</td>
<td>最小值</td>
</tr>
<tr>
<td>avg</td>
<td>平均值</td>
</tr>
<tr>
<td>sum</td>
<td>求和</td>
</tr>
</tbody></table>
<blockquote>
<p>count ：按照列去统计有多少行数据。</p>
<ul>
<li>在根据指定的列统计的时候，如果这一列中有null的行，该行不会被统计在其中。</li>
</ul>
<p>sum ：计算指定列的数值和，如果不是数值类型，那么计算结果为0</p>
<p>max ：计算指定列的最大值</p>
<p>min ：计算指定列的最小值</p>
<p>avg ：计算指定列的平均值</p>
</blockquote>
<p>案例1：统计该企业员工数量</p>
<p><code>count(*)  推荐此写法（MySQL底层进行了优化）</code></p>
<p><code>select count(*) from tb_emp;</code></p>
<p>案例2：统计该企业最早入职的员工</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select min(entrydate) from tb_emp;</span><br></pre></td></tr></table></figure>

<h3 id="分组查询"><a href="#分组查询" class="headerlink" title="分组查询"></a>分组查询</h3><p>分组： 按照某一列或者某几列，把相同的数据进行合并输出。</p>
<p>select  字段列表  from  表名  [where 条件]  group by 分组字段名  [having 分组后过滤条件];案例1：根据性别分组 , 统计男性和女性员工的数量</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">select gender, count(*)</span><br><span class="line">from tb_emp</span><br><span class="line">group by gender; -- 按照gender字段进行分组（gender字段下相同的数据归为一组）</span><br></pre></td></tr></table></figure>

<p>案例2：查询入职时间在 ‘2015-01-01’ (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">select job, count(*)</span><br><span class="line">from tb_emp</span><br><span class="line">where entrydate &lt;= &#x27;2015-01-01&#x27;   -- 分组前条件</span><br><span class="line">group by job                      -- 按照job字段分组</span><br><span class="line">having count(*) &gt;= 2;             -- 分组后条件</span><br></pre></td></tr></table></figure>

<p><strong>where与having区别（面试题）</strong></p>
<ul>
<li><p>执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。</p>
</li>
<li><p>判断条件不同：where不能对聚合函数进行判断，而having可以。</p>
<h3 id="排序查询"><a href="#排序查询" class="headerlink" title="排序查询"></a>排序查询</h3><p>排序在日常开发中是非常常见的一个操作，有升序排序，也有降序排序。</p>
<p>语法：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">select  字段列表  </span><br><span class="line">from   表名   </span><br><span class="line">[where  条件列表] </span><br><span class="line">[group by  分组字段 ] </span><br><span class="line">order  by  字段1  排序方式1 , 字段2  排序方式2 … ;</span><br></pre></td></tr></table></figure>

<ul>
<li><p>排序方式：</p>
<ul>
<li><p>ASC ：升序（默认值）</p>
</li>
<li><p>DESC：降序</p>
</li>
</ul>
</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">分页查询语法：</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span>  字段列表  <span class="keyword">from</span>   表名  limit  起始索引, 查询记录数 ;</span><br></pre></td></tr></table></figure>



<p>案例1：从起始索引0开始查询员工数据, 每页展示5条记录</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">select id, username, password, name, gender, image, job, entrydate, create_time, update_time</span><br><span class="line">from tb_emp</span><br><span class="line">limit 0 , 5; -- 从索引0开始，向后取5条记录</span><br></pre></td></tr></table></figure></li>
</ul>
<p>如果查询的是第1页数据，起始索引可以省略，直接简写为：limit 条数</p>
<p>员工性别统计：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">-- if(条件表达式, true取值 , false取值)</span><br><span class="line">select if(gender=1,&#x27;男性员工&#x27;,&#x27;女性员工&#x27;) AS 性别, count(*) AS 人数</span><br><span class="line">from tb_emp</span><br><span class="line">group by gender;</span><br></pre></td></tr></table></figure>

<p>员工职位统计：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">-- case 表达式 when 值1 then 结果1  when 值2  then  结果2 ...  else  result  end</span><br><span class="line">select (case job</span><br><span class="line">             when 1 then &#x27;班主任&#x27;</span><br><span class="line">             when 2 then &#x27;讲师&#x27;</span><br><span class="line">             when 3 then &#x27;学工主管&#x27;</span><br><span class="line">             when 4 then &#x27;教研主管&#x27;</span><br><span class="line">             else &#x27;未分配职位&#x27;</span><br><span class="line">        end) AS 职位 ,</span><br><span class="line">       count(*) AS 人数</span><br><span class="line">from tb_emp</span><br><span class="line">group by job;</span><br></pre></td></tr></table></figure>

<h3 id="5-多表设计"><a href="#5-多表设计" class="headerlink" title="5.多表设计"></a>5.多表设计</h3></article><div class="post-copyright"><div class="post-copyright__author"><span class="post-copyright-meta"><i class="fas fa-circle-user fa-fw"></i>Author: </span><span class="post-copyright-info"><a href="https://t0wolf.gitee.io/xiaopanblog">John Doe</a></span></div><div class="post-copyright__type"><span class="post-copyright-meta"><i class="fas fa-square-arrow-up-right fa-fw"></i>Link: </span><span class="post-copyright-info"><a href="https://t0wolf.gitee.io/xiaopanblog/2023/10/26/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B010.27/">https://t0wolf.gitee.io/xiaopanblog/2023/10/26/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B010.27/</a></span></div><div class="post-copyright__notice"><span class="post-copyright-meta"><i class="fas fa-circle-exclamation fa-fw"></i>Copyright Notice: </span><span class="post-copyright-info">All articles in this blog are licensed under <a target="_blank" rel="noopener" href="https://creativecommons.org/licenses/by-nc-sa/4.0/">CC BY-NC-SA 4.0</a> unless stating additionally.</span></div></div><div class="tag_share"><div class="post-meta__tag-list"></div><div class="post_share"><div class="social-share" data-image="https://i.loli.net/2021/02/24/5O1day2nriDzjSu.png" data-sites="facebook,twitter,wechat,weibo,qq"></div><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/butterfly-extsrc/sharejs/dist/css/share.min.css" media="print" onload="this.media='all'"><script src="https://cdn.jsdelivr.net/npm/butterfly-extsrc/sharejs/dist/js/social-share.min.js" defer></script></div></div><nav class="pagination-post" id="pagination"><div class="prev-post pull-left"><a href="./2023/10/28/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B010.28/" title="学习笔记10.28"><div class="cover" style="background: var(--default-bg-color)"></div><div class="pagination-info"><div class="label">Previous</div><div class="prev_info">学习笔记10.28</div></div></a></div><div class="next-post pull-right"><a href="./2023/10/26/%E2%80%9C%E7%AC%AC%E4%B8%80%E7%AF%87%E6%96%87%E7%AB%A0%E2%80%9D/" title="“第一篇文章”"><div class="cover" style="background: var(--default-bg-color)"></div><div class="pagination-info"><div class="label">Next</div><div class="next_info">“第一篇文章”</div></div></a></div></nav></div><div class="aside-content" id="aside-content"><div class="card-widget card-info"><div class="is-center"><div class="avatar-img"><img src="https://i.loli.net/2021/02/24/5O1day2nriDzjSu.png" onerror="this.onerror=null;this.src='./img/friend_404.gif'" alt="avatar"/></div><div class="author-info__name">John Doe</div><div class="author-info__description">一个简单的个人博客</div></div><div class="card-info-data site-data is-center"><a href="./archives/"><div class="headline">Articles</div><div class="length-num">10</div></a><a href="./tags/"><div class="headline">Tags</div><div class="length-num">1</div></a><a href="./categories/"><div class="headline">Categories</div><div class="length-num">0</div></a></div><a id="card-info-btn" target="_blank" rel="noopener" href="https://github.com/xxxxxx"><i class="fab fa-github"></i><span>Follow Me</span></a></div><div class="card-widget card-announcement"><div class="item-headline"><i class="fas fa-bullhorn fa-shake"></i><span>Announcement</span></div><div class="announcement_content">This is my Blog</div></div><div class="sticky_layout"><div class="card-widget" id="card-toc"><div class="item-headline"><i class="fas fa-stream"></i><span>Contents</span><span class="toc-percentage"></span></div><div class="toc-content"><ol class="toc"><li class="toc-item toc-level-2"><a class="toc-link" href="#1-autowired%E6%B3%A8%E8%A7%A3"><span class="toc-number">1.</span> <span class="toc-text">1.autowired注解</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#2-javaweb%E8%B5%84%E6%96%99mybatis%E9%85%8D%E7%BD%AE"><span class="toc-number">2.</span> <span class="toc-text">2.javaweb资料mybatis配置</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">2.1.</span> <span class="toc-text"></span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#-1"><span class="toc-number">2.2.</span> <span class="toc-text"></span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#-2"><span class="toc-number">2.3.</span> <span class="toc-text"></span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#3-MySQL%E5%A4%8D%E4%B9%A0"><span class="toc-number">3.</span> <span class="toc-text">3.MySQL复习</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1-SQL"><span class="toc-number">3.1.</span> <span class="toc-text">1.SQL</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-%E5%85%B3%E7%B3%BB%E5%9E%8B%E6%95%B0%E6%8D%AE%E5%BA%93%EF%BC%88RDBMS%EF%BC%89"><span class="toc-number">3.2.</span> <span class="toc-text">2.关系型数据库（RDBMS）</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-DDL"><span class="toc-number">3.3.</span> <span class="toc-text">3.DDL</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#4-DML"><span class="toc-number">3.4.</span> <span class="toc-text">4.DML</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E8%81%9A%E5%90%88%E5%87%BD%E6%95%B0"><span class="toc-number">3.5.</span> <span class="toc-text">聚合函数</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%88%86%E7%BB%84%E6%9F%A5%E8%AF%A2"><span class="toc-number">3.6.</span> <span class="toc-text">分组查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%8E%92%E5%BA%8F%E6%9F%A5%E8%AF%A2"><span class="toc-number">3.7.</span> <span class="toc-text">排序查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#5-%E5%A4%9A%E8%A1%A8%E8%AE%BE%E8%AE%A1"><span class="toc-number">3.8.</span> <span class="toc-text">5.多表设计</span></a></li></ol></li></ol></div></div><div class="card-widget card-recent-post"><div class="item-headline"><i class="fas fa-history"></i><span>Recent Post</span></div><div class="aside-list"><div class="aside-list-item no-cover"><div class="content"><a class="title" href="./2023/11/25/markdown%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/" title="markdown学习笔记">markdown学习笔记</a><time datetime="2023-11-25T06:47:13.000Z" title="Created 2023-11-25 14:47:13">2023-11-25</time></div></div><div class="aside-list-item no-cover"><div class="content"><a class="title" href="./2023/11/06/%E8%8B%8D%E7%A9%B9%E5%A4%96%E5%8D%96%E7%AC%94%E8%AE%B0day3/" title="苍穹外卖笔记day3">苍穹外卖笔记day3</a><time datetime="2023-11-06T01:36:00.000Z" title="Created 2023-11-06 09:36:00">2023-11-06</time></div></div><div class="aside-list-item no-cover"><div class="content"><a class="title" href="./2023/11/01/%E8%8B%8D%E7%A9%B9%E5%A4%96%E5%8D%96%E7%AC%94%E8%AE%B0day2/" title="苍穹外卖笔记day2">苍穹外卖笔记day2</a><time datetime="2023-11-01T13:05:25.000Z" title="Created 2023-11-01 21:05:25">2023-11-01</time></div></div><div class="aside-list-item no-cover"><div class="content"><a class="title" href="./2023/10/31/%E8%8B%8D%E7%A9%B9%E5%A4%96%E5%8D%96%E7%AC%94%E8%AE%B0day1/" title="苍穹外卖笔记day1">苍穹外卖笔记day1</a><time datetime="2023-10-31T09:13:57.000Z" title="Created 2023-10-31 17:13:57">2023-10-31</time></div></div><div class="aside-list-item no-cover"><div class="content"><a class="title" href="./2023/10/31/Spring%E7%AC%94%E8%AE%B0/" title="Spring笔记">Spring笔记</a><time datetime="2023-10-31T03:08:11.000Z" title="Created 2023-10-31 11:08:11">2023-10-31</time></div></div></div></div></div></div></main><footer id="footer"><div id="footer-wrap"><div class="copyright">&copy;2020 - 2024 By John Doe</div><div class="framework-info"><span>Framework </span><a target="_blank" rel="noopener" href="https://hexo.io">Hexo</a><span class="footer-separator">|</span><span>Theme </span><a target="_blank" rel="noopener" href="https://github.com/jerryc127/hexo-theme-butterfly">Butterfly</a></div></div></footer></div><div id="rightside"><div id="rightside-config-hide"><button id="readmode" type="button" title="Read Mode"><i class="fas fa-book-open"></i></button><button id="darkmode" type="button" title="Toggle Between Light And Dark Mode"><i class="fas fa-adjust"></i></button><button id="hide-aside-btn" type="button" title="Toggle between Single-column and Double-column"><i class="fas fa-arrows-alt-h"></i></button></div><div id="rightside-config-show"><button id="rightside-config" type="button" title="Setting"><i class="fas fa-cog fa-spin"></i></button><button class="close" id="mobile-toc-button" type="button" title="Table Of Contents"><i class="fas fa-list-ul"></i></button><button id="go-up" type="button" title="Back To Top"><span class="scroll-percent"></span><i class="fas fa-arrow-up"></i></button></div></div><div><script src="./js/utils.js"></script><script src="./js/main.js"></script><script src="https://cdn.jsdelivr.net/npm/@fancyapps/ui/dist/fancybox/fancybox.umd.min.js"></script><div class="js-pjax"></div><script async data-pjax src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script></div></body></html>